namespace Billing2
{
    using AjaxControlToolkit;
    using Microsoft.Reporting.WebForms;
    using Billing2.Controls;
    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    using Microsoft.Security.Application;
    using System.Configuration;
    using System.Text;
    using System.Security.Cryptography;
    using MyLib;

    public class GenerateBillReport : Page
    {
        protected Button BtnGenerate;
        protected ConfirmButtonExtender BtnGenerate_ConfirmButtonExtender;
        protected CheckBox chkboxOverwriteReport;
        protected DropDownList ddlBillPeriod;
        protected DropDownList ddlCostCentre;
        protected DropDownList ddlOrganization;
        protected DropDownList ddlSite;
        private DataTable dt;
        protected GridView gvwReports;
        protected ReportViewer ReportViewer1;
        protected ucHighlight ucHighlight1;
        protected ucPageTitle ucPageTitle1;
        protected UpdatePanel updatePanel1;
        protected UpdatePanel updatePanel2;
        protected UpdatePanel updatePanel7;
        protected UpdateProgress updateprogress1;

        private void BindBillPeriod()
        {
            ClsBillPeriod cls1 = new ClsBillPeriod();
            this.ddlBillPeriod.DataSource = cls1.SelectGeneratedDataPeriod();
            this.ddlBillPeriod.DataTextField = "ServiceMonth";
            this.ddlBillPeriod.DataValueField = "ID";
            this.ddlBillPeriod.DataBind();
            DataTable dtCurrentPeriod = cls1.SelectCurrentPreiod();
            if ((dtCurrentPeriod.Rows != null) && (dtCurrentPeriod.Rows.Count > 0))
            {
                this.BillPeriodID = dtCurrentPeriod.Rows[0]["ID"].ToString();
            }
        }

        private void BindDLLCostCentre()
        {
            ClsCostCentresInSite cls = new ClsCostCentresInSite();
            if (this.ddlSite.SelectedIndex <= 0)
            {
                cls.SiteID = 0;
            }
            else
            {
                cls.SiteID = Convert.ToInt64(this.ddlSite.SelectedValue);
            }
            DataTable dt1 = cls.SelectBySiteID();
            this.ddlCostCentre.Items.Clear();
            this.ddlCostCentre.Items.Add(new ListItem("ALL", "0"));
            for (int i = 0; i < dt1.Rows.Count; i++)
            {
                this.ddlCostCentre.Items.Add(new ListItem(Microsoft.Security.Application.Encoder.HtmlEncode(dt1.Rows[i]["CostCentreIDName"].ToString()), Microsoft.Security.Application.Encoder.HtmlEncode(dt1.Rows[i]["ID"].ToString())));
            }
        }

        private void BindDLLOrganization()
        {
            DataTable dt1 = new ClsOrganization().SelectAll();
            this.ddlOrganization.Items.Clear();
            this.ddlOrganization.Items.Add(new ListItem("ALL", "0"));
            for (int i = 0; i < dt1.Rows.Count; i++)
            {
                this.ddlOrganization.Items.Add(new ListItem(Microsoft.Security.Application.Encoder.HtmlEncode(dt1.Rows[i]["OrgIDName"].ToString()), Microsoft.Security.Application.Encoder.HtmlEncode(dt1.Rows[i]["ID"].ToString())));
            }
            this.BindDLLSitesInOrganization();
        }

        private void BindDLLSitesInOrganization()
        {
            ClsSitesInOrganization cls = new ClsSitesInOrganization();
            if (this.ddlOrganization.SelectedIndex <= 0)
            {
                cls.OrgID = 0;
            }
            else
            {
                cls.OrgID = Convert.ToInt64(this.ddlOrganization.SelectedValue);
            }
            DataTable dt1 = cls.SelectByOrgID();
            this.ddlSite.Items.Clear();
            this.ddlSite.Items.Add(new ListItem("ALL", "0"));
            for (int i = 0; i < dt1.Rows.Count; i++)
            {
                this.ddlSite.Items.Add(new ListItem(Microsoft.Security.Application.Encoder.HtmlEncode(dt1.Rows[i]["SiteIDName"].ToString()), Microsoft.Security.Application.Encoder.HtmlEncode(dt1.Rows[i]["ID"].ToString())));
            }
            this.BindDLLCostCentre();
        }

        private DataTable BindGridViewData()
        {
            this.dt = Common.db.Select(@"
                SELECT 
	                Locked = (CASE ParameterType
		                WHEN 'All' THEN (SELECT COUNT(*) FROM BillReportRepository
			                WHERE BillPeriodID = @BillPeriodID AND BillReportTemplateID = A.ID AND SiteID = 0 AND CostCentreID = 0 AND IsLatest = 1 AND Locked = 1)
		                WHEN 'Site' THEN (SELECT COUNT(*) FROM BillReportRepository
			                WHERE BillPeriodID = @BillPeriodID AND BillReportTemplateID = A.ID AND (@SiteID = 0 OR SiteID = @SiteID) AND IsLatest = 1 AND Locked = 1)
		                WHEN 'CostCentre' THEN (SELECT COUNT(*) FROM BillReportRepository
			                WHERE BillPeriodID = @BillPeriodID AND BillReportTemplateID = A.ID AND (@SiteID = 0 OR SiteID = @SiteID) AND (@CostCentreID = 0 OR CostCentreID = @CostCentreID) AND IsLatest = 1 AND Locked = 1)
		                ELSE 0 END
	                )
	                ,ID
	                ,ReportName
	                ,ReportPath
	                ,RenderFormat
	                ,ReportFileExt
	                ,ContentType
	                ,DatasetName
	                ,StoredProcName
	                ,ParameterType
	                ,ReportStatus
	                ,BillType
                FROM BillReportTemplate A
                WHERE ReportStatus = 1
                ", Convert.ToInt64(BillPeriodID)
                 //, Convert.ToInt64(this.ddlOrganization.SelectedValue)
                 , Convert.ToInt64(this.ddlSite.SelectedValue)
                 , Convert.ToInt64(this.ddlCostCentre.SelectedValue)
                 );
            if (this.dt != null)
            {
                this.gvwReports.DataSource = this.dt;
                this.gvwReports.DataBind();
            }
            return this.dt;
        }

        protected void BtnGenerate_Click(object sender, EventArgs e)
        {
            try
            {
                this.ucHighlight1.CloseAll();
                int iCheckCount = 0;
                foreach (GridViewRow row in this.gvwReports.Rows)
                {
                    CheckBox chkbox = (CheckBox)row.FindControl("GRChkBox");
                    if (chkbox.Checked)
                    {
                        iCheckCount++;
                    }
                }
                if (iCheckCount == 0)
                {
                    this.ucHighlight1.ErrorMessage = "Please select at least one report for processing";
                    return;
                }
                else if (this.ddlBillPeriod.SelectedIndex == -1)
                {
                    this.ucHighlight1.ErrorMessage = "Please select the Billing Period";
                    return;
                }
                else if (this.ddlOrganization.SelectedIndex == -1)
                {
                    this.ucHighlight1.ErrorMessage = "Please select the Organization";
                    return;
                }
                else if (this.ddlSite.SelectedIndex == -1)
                {
                    this.ucHighlight1.ErrorMessage = "Please select the Site";
                    return;
                }
                else if (this.ddlCostCentre.SelectedIndex == -1)
                {
                    this.ucHighlight1.ErrorMessage = "Please select the Cost Centre";
                    return;
                }

                DataTable dtOrg = null;
                DataTable dtSite = null;
                DataTable dtCC = null;
                DataTable dtBillCust = null;
                ClsOrganization clsOrg = new ClsOrganization();
                ClsSitesInOrganization clsSite = new ClsSitesInOrganization();
                ClsCostCentresInSite clsCC = new ClsCostCentresInSite();
                ClsBillCustomer clsBillCust = new ClsBillCustomer();
                if (Convert.ToInt64(this.ddlOrganization.SelectedValue) == 0)
                {
                    dtOrg = clsOrg.SelectAll();
                    dtSite = clsSite.SelectAll();
                    dtCC = clsCC.SelectAll();
                    dtBillCust = clsBillCust.SelectByBillType("");
                }
                else if (Convert.ToInt64(this.ddlSite.SelectedValue) == 0)
                {
                    clsOrg.ID = Convert.ToInt64(this.ddlOrganization.SelectedValue);
                    dtOrg = clsOrg.Select();
                    clsSite.OrgID = Convert.ToInt64(this.ddlOrganization.SelectedValue);
                    dtSite = clsSite.SelectByOrgID();
                    dtCC = clsCC.SelectOrgSiteCCByOrgID(Convert.ToInt64(this.ddlOrganization.SelectedValue));
                    dtBillCust = clsBillCust.SelectByBillTypeOrgID("", Convert.ToInt64(this.ddlOrganization.SelectedValue));
                }
                else
                {
                    clsOrg.ID = Convert.ToInt64(this.ddlOrganization.SelectedValue);
                    dtOrg = clsOrg.Select();
                    clsSite.ID = Convert.ToInt64(this.ddlSite.SelectedValue);
                    dtSite = clsSite.Select();
                    if (Convert.ToInt64(this.ddlCostCentre.SelectedValue) == 0)
                    {
                        clsCC.SiteID = Convert.ToInt64(this.ddlSite.SelectedValue);
                        dtCC = clsCC.SelectBySiteID();
                        dtBillCust = clsBillCust.SelectByBillTypeSiteID("", Convert.ToInt64(this.ddlSite.SelectedValue));
                    }
                    else
                    {
                        clsCC.ID = Convert.ToInt64(this.ddlCostCentre.SelectedValue);
                        dtCC = clsCC.SelectOrgSiteCCByID();
                        dtBillCust = clsBillCust.SelectByBillTypeSiteIDCostCentreID("", Convert.ToInt64(this.ddlSite.SelectedValue), Convert.ToInt64(this.ddlCostCentre.SelectedValue));
                    }
                }

                if ((dtCC == null) || (dtCC.Rows.Count == 0))
                {
                    this.ucHighlight1.ErrorMessage = "Please select the Cost Centre";
                    return;
                }
                if ((dtSite == null) || (dtSite.Rows.Count == 0))
                {
                    this.ucHighlight1.ErrorMessage = "Please select the Site";
                    return;
                }
                if (this.CheckIfReportLocked(dtOrg, dtSite, dtCC))
                {
                    this.ucHighlight1.ErrorMessage = "Existing Report(s) had been locked.";
                    return;
                }
                if (!this.chkboxOverwriteReport.Checked && this.CheckIfReportExist(dtSite, dtCC, dtBillCust))
                {
                    this.ucHighlight1.ErrorMessage = "Existing Report(s) found on the selected billing period. Unable to proceed.";
                    return;
                }

                (new ClsBillReportRepository()).Sp_BillReport_UpdateVersion(Convert.ToInt64(this.BillPeriodID));

                int iReportCount = 0;
                int iIgnoreCount = 0;
                ClsBillReportTemplate clsBillRptTemp = new ClsBillReportTemplate();
                foreach (GridViewRow row in this.gvwReports.Rows)
                {
                    CheckBox chk1 = (CheckBox)row.FindControl("GRChkBox");
                    if (chk1.Checked)
                    {
                        Label lblReportTempID = (Label)row.FindControl("lblReportTemplateID");
                        clsBillRptTemp.ID = Convert.ToInt64(lblReportTempID.Text);
                        DataTable dtBillRptTemp = clsBillRptTemp.Select();
                        if ((dtBillRptTemp != null) && (dtBillRptTemp.Rows.Count > 0))
                        {
                            int n = 0;
                            iReportCount += this.GenerateReport(dtBillRptTemp, dtOrg, dtSite, dtCC, dtBillCust, out n);
                            iIgnoreCount += n;
                        }
                    }
                }
                if (iReportCount > 0)
                {
                    this.ucHighlight1.Message = this.ucHighlight1.Message = iReportCount.ToString() + " report(s) had been generated successfully. " + iIgnoreCount.ToString() + " report(s) had been ignored.";
                }
                else
                {
                    this.ucHighlight1.Message = this.ucHighlight1.Message = iReportCount.ToString() + " report(s) had been generated. " + iIgnoreCount.ToString() + " report(s) had been ignored.";
                }
            }
            catch (Exception ex)
            {
                EPPLog.Logger.Error("Exception:\r\n Type:" + ex.GetType() + ";\r\n Message:" + ex.Message + ";\r\n Trace:" + ex.StackTrace);
            }
        }

        private bool CheckIfReportExist(DataTable dtSite, DataTable dtCC, DataTable dtBillCustomer)
        {
            ClsBillReportTemplate clsBillRptTemp = new ClsBillReportTemplate();
            foreach (GridViewRow row in this.gvwReports.Rows)
            {
                CheckBox chk1 = (CheckBox) row.FindControl("GRChkBox");
                if (chk1.Checked)
                {
                    Label lblReportTempID = (Label) row.FindControl("lblReportTemplateID");
                    clsBillRptTemp.ID = Convert.ToInt64(lblReportTempID.Text);
                    DataTable dtBillRptTemp = clsBillRptTemp.Select();
                    if ((dtBillRptTemp != null) && (dtBillRptTemp.Rows.Count > 0))
                    {
                        long CostCentreID;
                        long SiteID;
                        ClsBillReportRepository clsRepository;
                        DataTable dtRepo;
                        int i;
                        long iBillReportTemplateID = Convert.ToInt64(dtBillRptTemp.Rows[0]["ID"].ToString());
                        string sParameterType = dtBillRptTemp.Rows[0]["ParameterType"].ToString();
                        if (sParameterType.ToUpper() == "COSTCENTRE")
                        {
                            i = 0;
                            while (i < dtCC.Rows.Count)
                            {
                                CostCentreID = Convert.ToInt64(dtCC.Rows[i]["ID"].ToString());
                                SiteID = Convert.ToInt64(dtCC.Rows[i]["SiteID"].ToString());
                                clsRepository = new ClsBillReportRepository
                                {
                                    BillPeriodID = Convert.ToInt64(this.BillPeriodID),
                                    BillReportTemplateID = iBillReportTemplateID,
                                    SiteID = SiteID,
                                    CostCentreID = CostCentreID
                                };
                                dtRepo = clsRepository.SelectByBillPeriodIDBillReportTemplateIDSiteIDCostCentreID();
                                if ((dtRepo != null) && (dtRepo.Rows.Count > 0))
                                {
                                    return true;
                                }
                                i++;
                            }
                        }
                        else if (sParameterType.ToUpper() == "SITE")
                        {
                            i = 0;
                            while (i < dtSite.Rows.Count)
                            {
                                CostCentreID = 0;
                                SiteID = Convert.ToInt64(dtSite.Rows[i]["ID"].ToString());
                                clsRepository = new ClsBillReportRepository
                                {
                                    BillPeriodID = Convert.ToInt64(this.BillPeriodID),
                                    BillReportTemplateID = iBillReportTemplateID,
                                    SiteID = SiteID,
                                    CostCentreID = CostCentreID
                                };
                                dtRepo = clsRepository.SelectByBillPeriodIDBillReportTemplateIDSiteIDCostCentreID();
                                if ((dtRepo != null) && (dtRepo.Rows.Count > 0))
                                {
                                    return true;
                                }
                                i++;
                            }
                        }
                        else if (sParameterType.ToUpper() == "ALL")
                        {
                            CostCentreID = 0;
                            SiteID = 0;
                            clsRepository = new ClsBillReportRepository
                            {
                                BillPeriodID = Convert.ToInt64(this.BillPeriodID),
                                BillReportTemplateID = iBillReportTemplateID,
                                SiteID = SiteID,
                                CostCentreID = CostCentreID
                            };
                            dtRepo = clsRepository.SelectByBillPeriodIDBillReportTemplateIDSiteIDCostCentreID();
                            if ((dtRepo != null) && (dtRepo.Rows.Count > 0))
                            {
                                return true;
                            }
                        }
                        else if (sParameterType.ToUpper() == "BILLCUSTOMER")
                        {
                            long BillCustomerID;
                            i = 0;
                            while (i < dtBillCustomer.Rows.Count)
                            {
                                BillCustomerID = Convert.ToInt64(dtBillCustomer.Rows[i]["ID"].ToString());
                                clsRepository = new ClsBillReportRepository
                                {
                                    BillPeriodID = Convert.ToInt64(this.BillPeriodID),
                                    BillReportTemplateID = iBillReportTemplateID,
                                    BillCustomerID = BillCustomerID
                                };
                                dtRepo = clsRepository.SelectByBillPeriodIDBillReportTemplateIDBillCustomerID();
                                if ((dtRepo != null) && (dtRepo.Rows.Count > 0))
                                {
                                    return true;
                                }
                                i++;
                            }
                        }
                    }
                }
            }
            return false;
        }

        private bool CheckIfReportLocked(DataTable dtOrg, DataTable dtSite, DataTable dtCC)
        {
            ClsBillReportTemplate clsBillRptTemp = new ClsBillReportTemplate();
            foreach (GridViewRow row in this.gvwReports.Rows)
            {
                CheckBox chk1 = (CheckBox)row.FindControl("GRChkBox");
                if (chk1.Checked)
                {
                    Label lblReportTempID = (Label)row.FindControl("lblReportTemplateID");
                    Int64 TempID = Convert.ToInt64(lblReportTempID.Text);
                    int? locked = Common.db.SelectInt(@"
                        SELECT 
	                        Locked = (CASE ParameterType
		                        WHEN 'All' THEN (SELECT COUNT(*) FROM BillReportRepository
			                        WHERE BillPeriodID = @BillPeriodID AND BillReportTemplateID = A.ID AND SiteID = 0 AND CostCentreID = 0 AND IsLatest = 1 AND Locked = 1)
		                        WHEN 'Site' THEN (SELECT COUNT(*) FROM BillReportRepository
			                        WHERE BillPeriodID = @BillPeriodID AND BillReportTemplateID = A.ID AND (@SiteID = 0 OR SiteID = @SiteID) AND IsLatest = 1 AND Locked = 1)
		                        WHEN 'CostCentre' THEN (SELECT COUNT(*) FROM BillReportRepository
			                        WHERE BillPeriodID = @BillPeriodID AND BillReportTemplateID = A.ID AND (@SiteID = 0 OR SiteID = @SiteID) AND (@CostCentreID = 0 OR CostCentreID = @CostCentreID) AND IsLatest = 1 AND Locked = 1)
		                        ELSE 0 END
	                        )
                        FROM BillReportTemplate A
                        WHERE ReportStatus = 1
                            AND A.ID = @ID
                        ", Convert.ToInt64(BillPeriodID)
                        , Convert.ToInt64(this.ddlSite.SelectedValue)
                        , Convert.ToInt64(this.ddlCostCentre.SelectedValue)
                        , TempID
                        );
                    if (locked > 0)
                        return true;
                }
            }
            return false;
        }

        protected void ddlOrganization_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (this.ddlOrganization.SelectedIndex != -1)
            {
                this.BindDLLSitesInOrganization();
            }
            this.BindGridViewData();
        }

        protected void ddlSite_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (this.ddlSite.SelectedIndex != -1)
            {
                this.BindDLLCostCentre();
            }
            this.BindGridViewData();
        }

        protected void ddlCostCentre_SelectedIndexChanged(object sender, EventArgs e)
        {
            this.BindGridViewData();
        }

        protected void ddlBillPeriod_SelectedIndexChanged(object sender, EventArgs e)
        {
            this.BindGridViewData();
        }

        private void BillReportRepositoryMarkAsOldVersion(DataTable dtRepo)
        {
            ClsBillReportRepository clsRepository = new ClsBillReportRepository();
            if ((dtRepo != null) && (dtRepo.Rows.Count > 0))
            {
                for (int i = 0; i < dtRepo.Rows.Count; i++)
                {
                    clsRepository.ID = Convert.ToInt64(dtRepo.Rows[i]["ID"].ToString());
                    clsRepository.Delete();
                }
            }
        }

        private DataTable ExecuteReportStoreProc(string StoredProcName, long BillReportTemplateID)
        {
            if (!Common.ValidStoreProcedureName(StoredProcName))
                throw new Exception("invalid store procedure name. name='" + StoredProcName + "'");

            SqlCommand cm = new SqlCommand {
                CommandText = StoredProcName,
                CommandType = CommandType.StoredProcedure
            };
            cm.Parameters.Add(new SqlParameter("@BillPeriodID", Convert.ToInt64(this.BillPeriodID)));
            cm.Parameters.Add(new SqlParameter("@BillReportTemplateID", Convert.ToInt64(BillReportTemplateID)));
            DBManager dm = new DBManager();
            return dm.executeCommandDataTable(cm);
        }

        private DataTable ExecuteReportStoreProcByBillCustomer(string StoredProcName, long BillReportTemplateID, long BillCustomerID)
        {
            if (!Common.ValidStoreProcedureName(StoredProcName))
                throw new Exception("invalid store procedure name. name='" + StoredProcName + "'");

            SqlCommand cm = new SqlCommand {
                CommandText = StoredProcName,
                CommandType = CommandType.StoredProcedure
            };
            cm.Parameters.Add(new SqlParameter("@BillPeriodID", Convert.ToInt64(this.BillPeriodID)));
            cm.Parameters.Add(new SqlParameter("@BillReportTemplateID", Convert.ToInt64(BillReportTemplateID)));
            cm.Parameters.Add(new SqlParameter("@BillCustomerID", BillCustomerID));
            DBManager dm = new DBManager();
            return dm.executeCommandDataTable(cm);
        }

        private DataTable ExecuteReportStoreProcByCC(string StoredProcName, long BillReportTemplateID, long CostCentreID)
        {
            if (!Common.ValidStoreProcedureName(StoredProcName))
                throw new Exception("invalid store procedure name. name='" + StoredProcName + "'");

            SqlCommand cm = new SqlCommand {
                CommandText = StoredProcName,
                CommandType = CommandType.StoredProcedure
            };
            cm.Parameters.Add(new SqlParameter("@BillPeriodID", Convert.ToInt64(this.BillPeriodID)));
            cm.Parameters.Add(new SqlParameter("@BillReportTemplateID", Convert.ToInt64(BillReportTemplateID)));
            cm.Parameters.Add(new SqlParameter("@CostCentreID", CostCentreID));
            DBManager dm = new DBManager();
            return dm.executeCommandDataTable(cm);
        }

        private DataTable ExecuteReportStoreProcBySite(string StoredProcName, long BillReportTemplateID, long SiteID)
        {
            if (!Common.ValidStoreProcedureName(StoredProcName))
                throw new Exception("invalid store procedure name. name='" + StoredProcName + "'");

            SqlCommand cm = new SqlCommand {
                CommandText = StoredProcName,
                CommandType = CommandType.StoredProcedure
            };
            cm.Parameters.Add(new SqlParameter("@BillPeriodID", Convert.ToInt64(this.BillPeriodID)));
            cm.Parameters.Add(new SqlParameter("@BillReportTemplateID", Convert.ToInt64(BillReportTemplateID)));
            cm.Parameters.Add(new SqlParameter("@SiteID", SiteID));
            DBManager dm = new DBManager();
            return dm.executeCommandDataTable(cm);
        }

        private int GenerateReport(DataTable dtBillRptTemp, DataTable dtOrg, DataTable dtSite, DataTable dtCC, DataTable dtBillCustomer, out int ignore)
        {
            this.Culture = Microsoft.Security.Application.Encoder.HtmlEncode(ConfigurationManager.AppSettings["Culture"].ToString());

            Warning[] warnings;
            string[] streamids;
            string mimeType;
            string encoding;
            string filenameExtension;

            int iCount = 0;
            ignore = 0;
            long iBillReportTemplateID = Convert.ToInt64(dtBillRptTemp.Rows[0]["ID"].ToString());
            string sReportName = dtBillRptTemp.Rows[0]["ReportName"].ToString();
            string sReportPath = dtBillRptTemp.Rows[0]["ReportPath"].ToString();
            string sRenderFormat = dtBillRptTemp.Rows[0]["RenderFormat"].ToString();
            string sReportFileExt = dtBillRptTemp.Rows[0]["ReportFileExt"].ToString();
            string sContentType = dtBillRptTemp.Rows[0]["ContentType"].ToString();
            string sDatasetName = dtBillRptTemp.Rows[0]["DatasetName"].ToString();
            string sStoredProcName = dtBillRptTemp.Rows[0]["StoredProcName"].ToString();
            string sParameterType = dtBillRptTemp.Rows[0]["ParameterType"].ToString();
            string sBillType = dtBillRptTemp.Rows[0]["BillType"].ToString();
            string sIgnoreColumns = dtBillRptTemp.Rows[0]["IgnoreColumns"].ToString();
            string NewThumbnail = "";
            int NewVersion = 0;
            if (sParameterType.ToUpper() == "COSTCENTRE")
            {
                for (int i = 0; i < dtCC.Rows.Count; i++)
                {
                    long CostCentreID = Convert.ToInt64(dtCC.Rows[i]["ID"].ToString());
                    long SiteID = Convert.ToInt64(dtCC.Rows[i]["SiteID"].ToString());
                    string CostCentreIDName = dtCC.Rows[i]["CostCentreIDName"].ToString();
                    string SiteIDName = dtCC.Rows[i]["SiteIDName"].ToString();
                    long BillCustomerID = 0;
                    ClsBillCustomer clsBillCust = new ClsBillCustomer();
                    DataTable dtBillCust = clsBillCust.SelectByBillTypeSiteIDCostCentreID(sBillType, SiteID, CostCentreID);
                    if ((dtBillCust != null) && (dtBillCust.Rows.Count > 0))
                    {
                        BillCustomerID = Convert.ToInt64(dtBillCust.Rows[0]["ID"].ToString());
                    }
                    DataTable dtData = this.ExecuteReportStoreProcByCC(sStoredProcName, iBillReportTemplateID, CostCentreID);
                    if ((dtData != null) && (dtData.Rows.Count > 0))
                    {
                        //get new version thumbnail
                        if (!ProcessVersion(dtData, sIgnoreColumns, Convert.ToInt64(this.BillPeriodID), iBillReportTemplateID, false, SiteID, CostCentreID, BillCustomerID, ref NewThumbnail, ref NewVersion))
                        {
                            ignore++;
                            continue;
                        }

                        string sFileName = (CostCentreIDName + "_" + sReportName + "_V" + NewVersion.ToString() + sReportFileExt).Replace(" ", "_");
                        ReportDataSource rds = new ReportDataSource(sDatasetName, dtData);
                        this.ReportViewer1.LocalReport.ReportPath = sReportPath;
                        this.ReportViewer1.LocalReport.DataSources.Clear();
                        this.ReportViewer1.LocalReport.DataSources.Add(rds);
                        ReportParameter rptParamFileName = new ReportParameter("FileName", sFileName);
                        //EPPLog.Logger.Debug(sFileName);
                        this.ReportViewer1.LocalReport.SetParameters(rptParamFileName);
                        byte[] bytes = this.ReportViewer1.LocalReport.Render(sRenderFormat, null, out mimeType, out encoding, out filenameExtension, out streamids, out warnings);
                        ClsBillReportRepository clsRepository = new ClsBillReportRepository
                        {
                            BillPeriodID = Convert.ToInt64(this.BillPeriodID),
                            BillReportTemplateID = iBillReportTemplateID,
                            ReportFileName = sFileName,
                            ReportData = bytes,
                            ContentType = sContentType,
                            IsReleased = false,
                            SiteID = SiteID,
                            CostCentreID = CostCentreID,
                            CreatedBy = LoginUser.UserID,
                            DownloadID = ClsUtilityHelper.GenerateId(),
                            BillCustomerID = BillCustomerID,
                            Version = NewVersion,
                            IsLatest = 1,
                            Thumbnail = NewThumbnail
                        };
                        if (this.chkboxOverwriteReport.Checked)
                        {
                            DataTable dtRepo = clsRepository.SelectByBillPeriodIDBillReportTemplateIDSiteIDCostCentreID();
                            this.BillReportRepositoryMarkAsOldVersion(dtRepo);
                        }
                        long RepositoryId = clsRepository.Save();
                        SaveReportData(RepositoryId, iBillReportTemplateID, Convert.ToInt64(this.BillPeriodID), dtData);
                        iCount++;
                    }
                    else
                        DeleteOldVersion(Convert.ToInt64(this.BillPeriodID), iBillReportTemplateID, SiteID, CostCentreID);
                }
                return iCount;
            }
            if (sParameterType.ToUpper() == "SITE")
            {
                for (int i = 0; i < dtSite.Rows.Count; i++)
                {
                    long CostCentreID = 0;
                    long SiteID = Convert.ToInt64(dtSite.Rows[i]["ID"].ToString());
                    //string CostCentreIDName = "";
                    string SiteIDName = dtSite.Rows[i]["SiteIDName"].ToString();
                    long BillCustomerID = 0;
                    DataTable dtBillCust = new ClsBillCustomer().SelectByBillTypeSiteIDCostCentreID(sBillType, SiteID, CostCentreID);
                    if ((dtBillCust != null) && (dtBillCust.Rows.Count > 0))
                    {
                        BillCustomerID = Convert.ToInt64(dtBillCust.Rows[0]["ID"].ToString());
                    }
                    DataTable dtData = this.ExecuteReportStoreProcBySite(sStoredProcName, iBillReportTemplateID, SiteID);
                    if ((dtData != null) && (dtData.Rows.Count > 0))
                    {
                        //get new version thumbnail
                        if (!ProcessVersion(dtData, sIgnoreColumns, Convert.ToInt64(this.BillPeriodID), iBillReportTemplateID, false, SiteID, CostCentreID, BillCustomerID, ref NewThumbnail, ref NewVersion))
                        {
                            ignore++;
                            continue;
                        }

                        string sFileName = (SiteIDName + "_" + sReportName + "_V" + NewVersion.ToString() + sReportFileExt).Replace(" ", "_");
                        ReportDataSource rds = new ReportDataSource(sDatasetName, dtData);
                        this.ReportViewer1.LocalReport.ReportPath = sReportPath;
                        this.ReportViewer1.LocalReport.DataSources.Clear();
                        this.ReportViewer1.LocalReport.DataSources.Add(rds);
                        ReportParameter rptParamFileName = new ReportParameter("FileName", sFileName);
                        this.ReportViewer1.LocalReport.SetParameters(rptParamFileName);
                        byte[] bytes = this.ReportViewer1.LocalReport.Render(sRenderFormat, null, out mimeType, out encoding, out filenameExtension, out streamids, out warnings);
                        ClsBillReportRepository clsRepository = new ClsBillReportRepository
                        {
                            BillPeriodID = Convert.ToInt64(this.BillPeriodID),
                            BillReportTemplateID = iBillReportTemplateID,
                            ReportFileName = sFileName,
                            ReportData = bytes,
                            ContentType = sContentType,
                            IsReleased = false,
                            SiteID = SiteID,
                            CostCentreID = CostCentreID,
                            CreatedBy = LoginUser.UserID,
                            DownloadID = ClsUtilityHelper.GenerateId(),
                            BillCustomerID = BillCustomerID,
                            Version = NewVersion,
                            IsLatest = 1,
                            Thumbnail = NewThumbnail
                        };
                        if (this.chkboxOverwriteReport.Checked)
                        {
                            DataTable dtRepo = clsRepository.SelectByBillPeriodIDBillReportTemplateIDSiteIDCostCentreID();
                            this.BillReportRepositoryMarkAsOldVersion(dtRepo);
                        }
                        long RepositoryId = clsRepository.Save();
                        SaveReportData(RepositoryId, iBillReportTemplateID, Convert.ToInt64(this.BillPeriodID), dtData);
                        iCount++;
                    }
                    else
                        DeleteOldVersion(Convert.ToInt64(this.BillPeriodID), iBillReportTemplateID, SiteID, CostCentreID);
                }
                return iCount;
            }
            if (sParameterType.ToUpper() == "ALL")
            {
                long CostCentreID = 0;
                long SiteID = 0;
                long BillCustomerID = 0;
                DataTable dtData = this.ExecuteReportStoreProc(sStoredProcName, iBillReportTemplateID);
                if ((dtData != null) && (dtData.Rows.Count > 0))
                {
                    //get new version thumbnail
                    if (!ProcessVersion(dtData, sIgnoreColumns, Convert.ToInt64(this.BillPeriodID), iBillReportTemplateID, false, SiteID, CostCentreID, BillCustomerID, ref NewThumbnail, ref NewVersion))
                    {
                        ignore++;
                        return 0;
                    }

                    string sFileName = (sReportName + "_V" + NewVersion.ToString() + sReportFileExt).Replace(" ", "_");
                    ReportDataSource rds = new ReportDataSource(sDatasetName, dtData);
                    this.ReportViewer1.LocalReport.ReportPath = sReportPath;
                    this.ReportViewer1.LocalReport.DataSources.Clear();
                    this.ReportViewer1.LocalReport.DataSources.Add(rds);
                    ReportParameter rptParamFileName = new ReportParameter("FileName", sFileName);
                    this.ReportViewer1.LocalReport.SetParameters(rptParamFileName);
                    byte[] bytes = this.ReportViewer1.LocalReport.Render(sRenderFormat, null, out mimeType, out encoding, out filenameExtension, out streamids, out warnings);
                    ClsBillReportRepository clsRepository = new ClsBillReportRepository
                    {
                        BillPeriodID = Convert.ToInt64(this.BillPeriodID),
                        BillReportTemplateID = iBillReportTemplateID,
                        ReportFileName = sFileName,
                        ReportData = bytes,
                        ContentType = sContentType,
                        IsReleased = false,
                        SiteID = SiteID,
                        CostCentreID = CostCentreID,
                        CreatedBy = LoginUser.UserID,
                        DownloadID = ClsUtilityHelper.GenerateId(),
                        BillCustomerID = BillCustomerID,
                        Version = NewVersion,
                        IsLatest = 1,
                        Thumbnail = NewThumbnail
                    };
                    if (this.chkboxOverwriteReport.Checked)
                    {
                        DataTable dtRepo = clsRepository.SelectByBillPeriodIDBillReportTemplateIDSiteIDCostCentreID();
                        this.BillReportRepositoryMarkAsOldVersion(dtRepo);
                    }
                    long RepositoryId = clsRepository.Save();
                    SaveReportData(RepositoryId, iBillReportTemplateID, Convert.ToInt64(this.BillPeriodID), dtData);
                    iCount++;
                }
                else
                    DeleteOldVersion(Convert.ToInt64(this.BillPeriodID), iBillReportTemplateID, SiteID, CostCentreID);
                return iCount;
            }
            if (sParameterType.ToUpper() == "BILLCUSTOMER")
            {
                for (int i = 0; i < dtBillCustomer.Rows.Count; i++)
                {
                    long CostCentreID = 0;
                    long BillCustomerID = Convert.ToInt64(dtBillCustomer.Rows[i]["ID"].ToString());
                    long SiteID = Convert.ToInt64(dtBillCustomer.Rows[i]["SiteID"].ToString());
                    DataTable dtData = this.ExecuteReportStoreProcByBillCustomer(sStoredProcName, iBillReportTemplateID, BillCustomerID);
                    if ((dtData != null) && (dtData.Rows.Count > 0))
                    {
                        //get new version thumbnail
                        if (!ProcessVersion(dtData, sIgnoreColumns, Convert.ToInt64(this.BillPeriodID), iBillReportTemplateID, true, SiteID, CostCentreID, BillCustomerID, ref NewThumbnail, ref NewVersion))
                        {
                            ignore++;
                            continue;
                        }

                        string sFileName = (dtBillCustomer.Rows[i]["CustName1"].ToString() + "_" + sReportName + "_V" + NewVersion.ToString() + sReportFileExt).Replace(" ", "_");
                        ReportDataSource rds = new ReportDataSource(sDatasetName, dtData);
                        this.ReportViewer1.LocalReport.ReportPath = sReportPath;
                        this.ReportViewer1.LocalReport.DataSources.Clear();
                        this.ReportViewer1.LocalReport.DataSources.Add(rds);
                        ReportParameter rptParamFileName = new ReportParameter("FileName", sFileName);
                        this.ReportViewer1.LocalReport.SetParameters(rptParamFileName);
                        byte[] bytes = this.ReportViewer1.LocalReport.Render(sRenderFormat, null, out mimeType, out encoding, out filenameExtension, out streamids, out warnings);
                        ClsBillReportRepository clsRepository = new ClsBillReportRepository
                        {
                            BillPeriodID = Convert.ToInt64(this.BillPeriodID),
                            BillReportTemplateID = iBillReportTemplateID,
                            ReportFileName = sFileName,
                            ReportData = bytes,
                            ContentType = sContentType,
                            IsReleased = false,
                            SiteID = SiteID,
                            CostCentreID = CostCentreID,
                            CreatedBy = LoginUser.UserID,
                            DownloadID = ClsUtilityHelper.GenerateId(),
                            BillCustomerID = BillCustomerID,
                            Version = NewVersion,
                            IsLatest = 1,
                            Thumbnail = NewThumbnail
                        };
                        if (this.chkboxOverwriteReport.Checked)
                        {
                            DataTable dtRepo = clsRepository.SelectByBillPeriodIDBillReportTemplateIDBillCustomerID();
                            this.BillReportRepositoryMarkAsOldVersion(dtRepo);
                        }
                        long RepositoryId = clsRepository.Save();
                        SaveReportData(RepositoryId, iBillReportTemplateID, Convert.ToInt64(this.BillPeriodID), dtData);
                        iCount++;
                    }
                    else
                        DeleteOldVersion(Convert.ToInt64(this.BillPeriodID), iBillReportTemplateID, BillCustomerID);
                }
                return iCount;
            }            
            return iCount;
        }

        private bool ProcessVersion(
            DataTable Data,
            string IgnoreColumns,
            long BillPeriodID,
            long BillReportTemplateID,
            bool IsBillCustomer,
            long SiteID,
            long CostCentreID,
            long BillCustomerID,
            ref string NewThumbnail,
            ref int NewVersion)
        {
            NewThumbnail = GetThumbnail(Data, IgnoreColumns, BillCustomerID);
            NewVersion = (int)Data.Rows[0]["Version"];

            //get old version thumbnail
            string OldThumbnail = "";
            int OldVersion = 0;
            DataTable old_report;
            if (IsBillCustomer)
                old_report = (new ClsBillReportRepository()).Sp_BillReportRepository_SelectPreviousVersionByBillPeriodIDBillReportTemplateIDBillCustomerID(
                    BillPeriodID,
                    BillReportTemplateID,
                    BillCustomerID);
            else
                old_report = (new ClsBillReportRepository()).Sp_BillReportRepository_SelectPreviousVersionByBillPeriodIDBillReportTemplateIDSiteIDCostCentreID(
                    BillPeriodID,
                    BillReportTemplateID,
                    SiteID,
                    CostCentreID);
            if (old_report.Rows.Count > 0)
            {
                OldThumbnail = old_report.Rows[0]["Thumbnail"].ToString();
                OldVersion = Convert.ToInt32(old_report.Rows[0]["Version"].ToString());
            }

            //if exist and not changes, ignore this report
            if (NewThumbnail == OldThumbnail)
                return false;

            //EPPLog.Logger.Debug("----------{0}/{1}/{2}/{3}/{4}----------\r\n{5}", BillPeriodID, BillReportTemplateID, SiteID, CostCentreID, NewVersion, DataTableToString(Data, IgnoreColumns));

            //if version is same, delete old one
            if (OldVersion == NewVersion)
            {
                ClsBillReportRepository cls1 = new ClsBillReportRepository();
                cls1.ID = Convert.ToInt64(old_report.Rows[0]["ID"].ToString());
                cls1.Delete2();

                cls1.BillReportTemplateID = Convert.ToInt64(old_report.Rows[0]["BillReportTemplateID"].ToString());
                //cls1.BillPeriodID = Convert.ToInt64(old_report.Rows[0]["BillPeriodID"].ToString());
                DeleteReportData(cls1.ID, cls1.BillReportTemplateID);
            }

            return true;
        }

        private void DeleteOldVersion(
             long BillPeriodID,
             long BillReportTemplateID,
             long SiteID,
             long CostCentreID
            )
        {
            (new ClsBillReportRepository()).Sp_BillReportRepository_DeleteOldVersionByBillPeriodIDBillReportTemplateIDSiteIDCostCentreID(
                BillPeriodID,
                BillReportTemplateID,
                SiteID,
                CostCentreID);
        }

        private void DeleteOldVersion(
            long BillPeriodID,
            long BillReportTemplateID,
            long BillCustomerID
        )
        {
            (new ClsBillReportRepository()).Sp_BillReportRepository_DeleteOldVersionByBillPeriodIDBillReportTemplateIDBillCustomerID(
                BillPeriodID,
                BillReportTemplateID,
                BillCustomerID);
        }

        private string GetThumbnail(DataTable src, string sIgnoreColumns, long BillCustomerID)
        {
            string s = BillCustomerID.ToString() + DataTableToString(src, sIgnoreColumns);
            MD5CryptoServiceProvider md5CSP = new MD5CryptoServiceProvider();
            byte[] bytes = System.Text.Encoding.Unicode.GetBytes(s);
            byte[] result = md5CSP.ComputeHash(bytes);
            md5CSP = null;
            string base64 = Convert.ToBase64String(result);
            return base64;
        }

        private string DataTableToString(DataTable src, string sIgnoreColumns)
        {
            DataTable dt = src.Copy();
            string[] IgnoreColumns;
            if (sIgnoreColumns == "")
                IgnoreColumns = new string[0];
            else
                IgnoreColumns = sIgnoreColumns.Split(new char[] { ',' });
            int i = 0;
            while (i < dt.Columns.Count)
            {
                bool found = false;
                for (int j = 0; j < IgnoreColumns.Length; j++)
                {
                    if (dt.Columns[i].ColumnName.Equals(IgnoreColumns[j], StringComparison.OrdinalIgnoreCase))
                    {
                        dt.Columns.Remove(dt.Columns[i]);
                        found = true;
                        break;
                    }
                }
                if (!found)
                    i++;
            }
            return _DataTableToString(dt);
        }

        private string _DataTableToString(DataTable dt)
        {
            StringBuilder sb = new StringBuilder();
            if (dt.Rows.Count > 0)
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    for (int j = 0; j < dt.Columns.Count; j++)
                        sb.Append("\"" + dt.Columns[j].ColumnName.ToString() + "\":\"" + dt.Rows[i][j].ToString() + "\"" + (j == dt.Columns.Count - 1? "" : ","));
                    sb.Append("\r\n");
                }
            return sb.ToString();
        } 

        private void SaveReportData(long RepositoryId, long TemplateID, long BillPeriodID, DataTable dtData)
        {
            if (TemplateID == 1)
            {
                foreach (DataRow row in dtData.Rows)
                {
                    DBCommands2.TBL_BillReportData_1 data = DBCommands2.TBL_BillReportData_1_ReadRow(row);
                    Common.db.BillReportData_1.insert(
                        RepositoryId,
                        TemplateID,
                        BillPeriodID,
                        data.Version,
                        data.Year0,
                        data.Month0,
                        data.StartDate,
                        data.EndDate,
                        data.OrgIDName,
                        data.SiteIDName,
                        data.CostCentreIDName,                       
                        data.ServiceFormID,
                        data.OrgID,
                        data.SiteID,
                        data.CostCentreID,
                        data.Email,
                        data.EffectiveDate,
                        data.TerminatedDate,
                        data.UnitPrice,
                        data.OneTimeCharge,
                        data.Remarks,
                        data.AssetID,
                        data.Username);
                }
            }
            else if (TemplateID == 9)
            {
                foreach (DataRow row in dtData.Rows)
                {
                    DBCommands2.TBL_BillReportData_1 data = DBCommands2.TBL_BillReportData_1_ReadRow(row);
                    Common.db.BillReportData_9.insert(
                        RepositoryId,
                        TemplateID,
                        BillPeriodID,
                        data.Version,
                        data.Year0,
                        data.Month0,
                        data.StartDate,
                        data.EndDate,
                        data.OrgIDName,
                        data.SiteIDName,
                        data.CostCentreIDName,
                        data.ServiceFormID,
                        data.OrgID,
                        data.SiteID,
                        data.CostCentreID,
                        data.Email,
                        data.EffectiveDate,
                        data.TerminatedDate,
                        data.UnitPrice,
                        data.OneTimeCharge,
                        data.Remarks,
                        data.AssetID,
                        data.Username);
                }
            }
        }

        private void DeleteReportData(long RepositoryId, long TemplateID)
        {
            if (TemplateID == 1)
            {
                Common.db.Exec("delete from BillReportData_1 where BillReportRepositoryID = @id", RepositoryId);
            }
        }

        private void GetPermission()
        {
            ClsACLHelper cls = new ClsACLHelper();
            string pageName = this.Page.ToString().Substring(4, this.Page.ToString().Substring(4).Length - 5) + ".aspx";
            cls.SetPermission(LoginUser.TreeMenu, pageName);
            this.BtnGenerate.Visible = cls.Create0;
            if (!cls.Read0)
            {
                base.Response.Redirect("logout.aspx",true);
            }
        }

        protected void GRChkALL_CheckedChanged(object sender, EventArgs e)
        {
            CheckBox chkALL = (CheckBox) this.gvwReports.HeaderRow.FindControl("GRChkALL");
            foreach (GridViewRow row in this.gvwReports.Rows)
            {
                CheckBox chk1 = (CheckBox) row.FindControl("GRChkBox");
                if (chkALL.Checked && chk1.Enabled)
                {
                    chk1.Checked = true;
                }
                else
                {
                    chk1.Checked = false;
                }
            }
        }

        protected void GRChkBox_CheckedChanged(object sender, EventArgs e)
        {
            bool bAllChecked = true;
            CheckBox chk1 = (CheckBox) sender;
            CheckBox chkALL = (CheckBox) this.gvwReports.HeaderRow.FindControl("GRChkALL");
            if (!chk1.Checked)
            {
                chkALL.Checked = false;
            }
            else if (chk1.Checked)
            {
                foreach (GridViewRow row in this.gvwReports.Rows)
                {
                    CheckBox chk2 = (CheckBox) row.FindControl("GRChkBox");
                    if (!chk2.Checked)
                    {
                        bAllChecked = false;
                    }
                }
                if (bAllChecked)
                {
                    chkALL.Checked = true;
                }
            }
        }

        protected void gvwReports_Sorting(object sender, GridViewSortEventArgs e)
        {
            this.dt = new DataView(this.BindGridViewData()) { Sort = e.SortExpression + " " + this.ViewState["SortDirection"].ToString() }.ToTable();
            if (this.ViewState["SortDirection"].ToString() == "ASC")
            {
                this.ViewState["SortDirection"] = "DESC";
            }
            else
            {
                this.ViewState["SortDirection"] = "ASC";
            }
            this.gvwReports.DataSource = this.dt;
            this.gvwReports.DataBind();
        }

        protected void Page_Load(object sender, EventArgs e)
        {
            this.ucHighlight1.CloseAll();
            if (this.Session["SiteID"] == null)
            {
                base.Response.Redirect("default.aspx",true);
            }
            if (!this.Page.IsPostBack)
            {
                this.ViewState["SortDirection"] = "ASC";
                this.BindBillPeriod();
                this.BindDLLOrganization();
                this.BindGridViewData();
            }
            this.GetPermission();
        }

        protected override void OnInit(EventArgs e)
        {
            base.OnInit(e);
            ViewStateUserKey = Session.SessionID;
        }

        public string BillPeriodID
        {
            get
            {
                if (this.ddlBillPeriod.SelectedIndex == -1)
                {
                    return "0";
                }
                return this.ddlBillPeriod.SelectedValue;
            }
            set
            {
                this.ddlBillPeriod.SelectedIndex = -1;
                if ((value != "") && (value != "0"))
                {
                    this.ddlBillPeriod.SelectedIndex = this.ddlBillPeriod.Items.IndexOf(this.ddlBillPeriod.Items.FindByValue(value));
                }
            }
        }

  
    }
}
